declare @date_update nvarchar(100)= (select [DATE_UPDATE] from [DATE_EM_REPORT])
declare @date_last_month nvarchar(100)= (SELECT DATEADD(m,2,CONVERT(date,@date_update)) )

IF EXISTS(SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'LC_EXPIRY_RAW')
        DROP TABLE LC_EXPIRY_RAW

IF EXISTS(SELECT *
         FROM DBO.SYSOBJECTS O
        WHERE O.XTYPE IN ('U') AND O.NAME = 'LC_CIF')
        DROP TABLE LC_CIF
--select * from lc_cif
		SELECT ROW_NUMBER() OVER(PARTITION BY B.ID ORDER BY B.debit_value_date DESC) AS RN,*
		 into LC_CIF 
		 from SERVER12.ANHCP.[DBO].[DOANH_SO_TTR_LC_DP] b where loai_giao_dich = 'LC' and cif is not null

  SELECT ROW_NUMBER() OVER(PARTITION BY B.TRANS_ID ORDER BY B.BUSINESS_DATE DESC) AS RN, B.* 
  INTO LC_EXPIRY_RAW 
  FROM SERVER16.[VPB_WHR2].[DBO].[TBL_LC_DR_VPB] B
  WHERE  EXISTS (SELECT *
                   FROM   LC_CIF A 
                   WHERE a.RN = 1 and A.ID = B.trans_id) AND B.EXPIRY_DATE BETWEEN @date_update AND @date_last_month

  DROP TABLE lc_expiry_trans

  SELECT * INTO LC_EXPIRY_TRANS FROM LC_EXPIRY_RAW WHERE RN = '1' ORDER BY RN

  select * from LC_EXPIRY_RAW

  DECLARE @MAXDATECRBALL DATETIME = (SELECT MAX(BUSINESS_DATE) FROM SERVER16.[VPB_WHR2].[DBO].CRB_ALL)
  --drop table LC_EXPIRTY_SO_DU
SELECT *
INTO LC_EXPIRTY_SO_DU
FROM SERVER16.[VPB_WHR2].[DBO].CRB_ALL b
WHERE BUSINESS_DATE = @MAXDATECRBALL AND GL ='9250' AND EXISTS (SELECT *
                   FROM   LC_CIF A 
                   WHERE  A.ID = B.id)

select * from LC_EXPIRTY_SO_DU a, LC_EXPIRY_TRANS b
where b.trans_id = a.id

SELECT * FROM LC_EXPIRY_TRANS

select top 10 * from  SERVER16.[VPB_WHR2].[DBO].CRB_ALL where business_date='20170329'
and GL ='9250' and Id in (select id from LC_CIF)

select * from LC_EXPIRTY_SO_DU

  DROP TABLE TBL_CM_ALERT_LC

  SELECT A.BUSINESS_DATE,TRANS_ID, EXPIRY_DATE, ISSUE_DATE, CO_CODE AS BRANCH_ID, B.BRANCH_CODE_SME, B.BRANCH_NAME_SME, APP_CUST_ID AS CIF, A.DAO AS DAO_TRANS, D.DAO_NAME AS DAO_TRANS_NAME, C.DAO AS DAO_CIF,  C.DAO_NAME , C.CUS_NAME, C.INDUSTRY_NAME, C.INDUSTRY_NAME_EN, B.REGION,e.product_name
  --INTO TBL_CM_ALERT_LC  
  FROM LC_EXPIRY_TRANS A, TBL_BRANCH B, TBL_CUSTOMER C, TBL_HR D,LC_CIF e
  ,(select SUM(AMOUNT_LCY*-1) AS SO_DU_LC, id from LC_EXPIRTY_SO_DU group by id) as f
  WHERE A.APP_CUST_ID = C.CIF AND B.BRANCH_ID=A.CO_CODE AND A.DAO = D.DAO and a.trans_id= e.id and e.RN = 1 and e.id = f.id
  --WHERE EXPIRY_DATE < '20160926'
  ORDER BY A.DAO

  select * from LC_CIF
--  INSERT TBL_DATE_BUSINESS
--select max(BUSINESS_DATE) as date_data, 'TBL_CM_ALERT_LC' from TBL_CM_ALERT_LC


--select * from SERVER12.ANHCP.[DBO].[DOANH_SO_TTR_LC_DP] where id = 'TF162150100801'
--select * from LC_EXPIRY_RAW where trans_id = 'TF162150100801' and rn= 1
update b
set b.date_data = a.BUSINESS_DATE
from (select max(business_date) as BUSINESS_DATE from TBL_CM_ALERT_LC) a,  TBL_DATE_BUSINESS b
where b.name_table = 'TBL_CM_ALERT_LC'

--select * from SERVER12.ANHCP.[DBO].[DOANH_SO_TTR_LC_DP]

-- DELETE FROM [DBCUSTOMER].DBO.TBL_CM_ALERT_LC
--INSERT INTO [DBCUSTOMER].DBO.TBL_CM_ALERT_LC
--SELECT * FROM TBL_CM_ALERT_LC

-- EXEC('IF EXISTS
--      (SELECT *
--         FROM [SERVER12].[SMECUSTOMER360].DBO.SYSOBJECTS O
--        WHERE O.XTYPE IN (''U'') AND O.NAME = ''TBL_CM_ALERT_LC'')
--        DELETE FROM [SERVER12].[SMECUSTOMER360].DBO.TBL_CM_ALERT_LC')
 
--INSERT [SERVER12].[SMECUSTOMER360].DBO.TBL_CM_ALERT_LC
--SELECT * FROM TBL_CM_ALERT_LC order by expiry_date
--select * FROM [SERVER12].[SMECUSTOMER360].DBO.TBL_CM_ALERT_LC

--select b.product_name, a.* from TBL_CM_ALERT_LC a,  SERVER12.ANHCP.[DBO].[DOANH_SO_TTR_LC_DP] b
--where a.trans_id= b.id